﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using SubSonic;
using CXDB;
using Coolite.Ext.Web;
public partial class import_xls : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Cookies.getCookies("clevelid") != "2")
        {
            Response.Redirect("~/err.aspx");
        }
        if(!IsPostBack)
        {
            Button2.Disabled = true;
        } 
         
    }
    protected void upload(object s,AjaxEventArgs e)
    {
        if (f.PostedFile.ContentLength != 0)
        {
            string filename = f.PostedFile.FileName.ToLower();
            string guid = Guid.NewGuid().ToString();
            Hidden1.Value = guid;
            string path = HttpContext.Current.Server.MapPath("~/import/file/");
            if (filename.Substring(filename.IndexOf(".")) == ".xls")
            {
                f.PostedFile.SaveAs(path + guid + ".xls");
                Button2.Disabled = false;
                Err.Warning("上传成功！",MessageBox.Icon.NONE);
            }
            else
            {
                Err.Warning("只能上传Excel文档(文档后缀名为.xls)。",MessageBox.Icon.ERROR);
                return;
            }
            
        }
        else
        {
            Err.Warning("上传失败！", MessageBox.Icon.ERROR);
        }
    }
    protected void import(object s,AjaxEventArgs e)
    {
        string filepath = Server.MapPath("~/import/file/"+Hidden1.Value.ToString()+".xls");
        foreach (string str in ExcelSheetName(filepath))
        {
            DataSet ds = ExcelDataSource(filepath, str);
            foreach (DataRow dr in ds.Tables["TABLE"].Rows)
            {
                Query q = new Query(CxCompanyBase.Schema);
                q.AddWhere("companyid", dr[0].ToString());
                if (q.GetRecordCount() == 0)
                {
                    CxCompanyBase.Insert(
                        dr[0].ToString(),
                        dr[1].ToString(),
                        dr[2].ToString(),
                        int.Parse(dr[3].ToString()),
                        int.Parse(dr[4].ToString()),
                        dr[5].ToString(),
                        int.Parse(dr[6].ToString()),
                        80,
                        "A",
                        "0",
                        0,
                        0,
                        DateTime.Now,
                        Guid.NewGuid());
                }
            }
        }
        Button2.Disabled = true;
        Ext.MessageBox.Show(new MessageBox.Config
        {
            Buttons = MessageBox.Button.OK,
            Icon = MessageBox.Icon.INFO,
            Title = "信息",
            Message = "导入成功"
        });
    }
    public DataSet ExcelDataSource(string filepath, string sheetname)
    {
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
        DataSet ds = new DataSet();
        oada.Fill(ds);
        return ds;
    }
    public ArrayList ExcelSheetName(string filepath)
    {
        ArrayList al = new ArrayList();
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();
        DataTable sheetNames = conn.GetOleDbSchemaTable
        (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        conn.Close();
        foreach (DataRow dr in sheetNames.Rows)
        {
            al.Add(dr[2]);
        }
        return al;
    }
}
